#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库配置初始化脚本

此脚本用于将静态配置文件中的设置导入到数据库中，
包括品牌、单位、营销词、系统配置和单位转换规则。

使用方法:
    python -m backend.scripts.init_db_config
"""

import os
import sys
import logging
from typing import List, Dict, Any, Optional
import datetime

# 将项目根目录添加到Python路径
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "../..")))

from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from backend.database.database import engine, Base, get_db
from backend.data_clean.models import (
    BrandConfig, CommonProductName, UnitConfig, 
    MarketingWordConfig, SystemConfig, UnitConversion
)
from backend.config.config import BRANDS, UNITS, REMOVE_WORDS

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s - %(name)s - %(levelname)s - %(message)s",
)
logger = logging.getLogger("init_db_config")

# 单位转换规则配置示例
UNIT_CONVERSIONS = {
    "重量单位": [
        {"source": "千克", "target": "克", "formula": "x * 1000", "description": "千克转克"},
        {"source": "kg", "target": "g", "formula": "x * 1000", "description": "千克转克"},
        {"source": "公斤", "target": "克", "formula": "x * 1000", "description": "公斤转克"},
        {"source": "斤", "target": "克", "formula": "x * 500", "description": "斤转克"},
        {"source": "两", "target": "克", "formula": "x * 50", "description": "两转克"},
        {"source": "g", "target": "mg", "formula": "x * 1000", "description": "克转毫克"},
        {"source": "克", "target": "毫克", "formula": "x * 1000", "description": "克转毫克"},
    ],
    "长度单位": [
        {"source": "米", "target": "厘米", "formula": "x * 100", "description": "米转厘米"},
        {"source": "m", "target": "cm", "formula": "x * 100", "description": "米转厘米"},
        {"source": "厘米", "target": "毫米", "formula": "x * 10", "description": "厘米转毫米"},
        {"source": "cm", "target": "mm", "formula": "x * 10", "description": "厘米转毫米"},
        {"source": "英寸", "target": "厘米", "formula": "x * 2.54", "description": "英寸转厘米"},
    ],
    "体积单位": [
        {"source": "升", "target": "毫升", "formula": "x * 1000", "description": "升转毫升"},
        {"source": "L", "target": "ml", "formula": "x * 1000", "description": "升转毫升"},
        {"source": "l", "target": "ml", "formula": "x * 1000", "description": "升转毫升"},
    ],
}

# 系统配置示例
SYSTEM_CONFIGS = [
    {
        "key": "data_cleaning_rules", 
        "value": '{"remove_marketing_words": true, "normalize_units": true, "extract_specifications": true}',
        "description": "数据清洗规则默认配置"
    },
    {
        "key": "ui_theme", 
        "value": "light",
        "description": "UI主题设置"
    },
    {
        "key": "max_upload_size", 
        "value": "10",
        "description": "最大上传文件大小(MB)"
    },
]

def init_brands(db: Session) -> None:
    """初始化品牌配置"""
    logger.info("开始初始化品牌配置...")
    created_count = 0
    
    try:
        for brand_name in BRANDS:
            # 检查是否已存在
            existing = db.query(BrandConfig).filter(BrandConfig.name == brand_name).first()
            if existing:
                continue
            
            # 创建新品牌
            new_brand = BrandConfig(
                name=brand_name,
                is_active=True
            )
            db.add(new_brand)
            created_count += 1
        
        db.commit()
        logger.info(f"成功初始化品牌配置，新增了 {created_count} 条记录")
    
    except Exception as e:
        db.rollback()
        logger.error(f"初始化品牌配置失败: {str(e)}")

def init_units(db: Session) -> None:
    """初始化单位配置"""
    logger.info("开始初始化单位配置...")
    created_count = 0
    
    try:
        # 单位类型映射
        unit_types = {
            "重量单位": ["克", "g", "kg", "千克", "公斤", "斤", "两", "吨", "t", "mg", "毫克"],
            "长度单位": ["米", "m", "cm", "厘米", "mm", "毫米", "英寸", "英尺"],
            "体积单位": ["升", "L", "l", "毫升", "ml", "立方米", "立方厘米"],
            "数量单位": ["个", "只", "条", "片", "块", "张", "件", "双", "套", "对", "组", "盒", 
                    "包", "袋", "箱", "瓶", "罐", "听", "桶", "支"]
        }
        
        # 创建反向映射
        unit_to_type = {}
        for type_name, units in unit_types.items():
            for unit in units:
                unit_to_type[unit] = type_name
        
        for unit_name in UNITS:
            # 检查是否已存在
            existing = db.query(UnitConfig).filter(UnitConfig.name == unit_name).first()
            if existing:
                continue
            
            # 获取单位类型
            unit_type = unit_to_type.get(unit_name, "其他")
            
            # 创建新单位
            new_unit = UnitConfig(
                name=unit_name,
                type=unit_type,
                is_active=True
            )
            db.add(new_unit)
            created_count += 1
        
        db.commit()
        logger.info(f"成功初始化单位配置，新增了 {created_count} 条记录")
    
    except Exception as e:
        db.rollback()
        logger.error(f"初始化单位配置失败: {str(e)}")

def init_marketing_words(db: Session) -> None:
    """初始化营销词配置"""
    logger.info("开始初始化营销词配置...")
    created_count = 0
    
    try:
        # 营销词分类映射
        word_types = {
            "促销词": ["特价", "促销", "限时", "打折", "秒杀", "折扣", "优惠", "满减", "包邮", "限购", "爆款", "VIP专享", "新品"],
            "描述词": ["热销", "畅销", "精选", "正品", "热卖", "超值", "实惠", "经典", "人气", "爆红", "网红", "明星同款", "大牌"],
            "时间词": ["春季", "夏季", "秋季", "冬季", "节日", "限定", "新款", "当季", "预售", "预约", "首发"]
        }
        
        # 创建反向映射
        word_to_type = {}
        for type_name, words in word_types.items():
            for word in words:
                word_to_type[word] = type_name
        
        for word in REMOVE_WORDS:
            # 检查是否已存在
            existing = db.query(MarketingWordConfig).filter(MarketingWordConfig.word == word).first()
            if existing:
                continue
            
            # 获取词语类型
            word_type = word_to_type.get(word, "其他")
            
            # 创建新营销词
            new_word = MarketingWordConfig(
                word=word,
                category=word_type,
                is_active=True
            )
            db.add(new_word)
            created_count += 1
        
        db.commit()
        logger.info(f"成功初始化营销词配置，新增了 {created_count} 条记录")
    
    except Exception as e:
        db.rollback()
        logger.error(f"初始化营销词配置失败: {str(e)}")

def init_system_configs(db: Session) -> None:
    """初始化系统配置"""
    logger.info("开始初始化系统配置...")
    created_count = 0
    
    try:
        for config in SYSTEM_CONFIGS:
            # 检查是否已存在
            existing = db.query(SystemConfig).filter(SystemConfig.key == config["key"]).first()
            if existing:
                # 更新现有配置
                existing.value = config["value"]
                existing.description = config["description"]
                continue
            
            # 创建新配置
            new_config = SystemConfig(
                key=config["key"],
                value=config["value"],
                description=config["description"]
            )
            db.add(new_config)
            created_count += 1
        
        db.commit()
        logger.info(f"成功初始化系统配置，新增了 {created_count} 条记录")
    
    except Exception as e:
        db.rollback()
        logger.error(f"初始化系统配置失败: {str(e)}")

def init_unit_conversions(db: Session) -> None:
    """初始化单位转换规则"""
    logger.info("开始初始化单位转换规则...")
    created_count = 0
    
    try:
        for unit_type, conversions in UNIT_CONVERSIONS.items():
            for conv in conversions:
                # 检查是否已存在
                existing = db.query(UnitConversion).filter(
                    UnitConversion.source_unit == conv["source"],
                    UnitConversion.target_unit == conv["target"]
                ).first()
                
                if existing:
                    # 更新现有规则
                    existing.conversion_formula = conv["formula"]
                    existing.description = conv["description"]
                    continue
                
                # 创建新规则
                new_conv = UnitConversion(
                    source_unit=conv["source"],
                    target_unit=conv["target"],
                    conversion_formula=conv["formula"],
                    description=conv["description"],
                    is_active=True
                )
                db.add(new_conv)
                created_count += 1
        
        db.commit()
        logger.info(f"成功初始化单位转换规则，新增了 {created_count} 条记录")
    
    except Exception as e:
        db.rollback()
        logger.error(f"初始化单位转换规则失败: {str(e)}")

def init_db():
    """初始化数据库配置"""
    logger.info("开始初始化数据库配置...")
    
    # 创建所有表（如果不存在）
    Base.metadata.create_all(bind=engine)
    logger.info("已确保数据库表存在")
    
    # 获取数据库会话
    session = next(get_db())
    
    try:
        # 初始化各配置
        init_brands(session)
        init_units(session)
        init_unit_conversions(session)
        init_marketing_words(session)
        init_system_configs(session)
        
        logger.info("数据库配置初始化完成!")
    
    except Exception as e:
        logger.error(f"数据库配置初始化过程中发生错误: {str(e)}")
    
    finally:
        session.close()

if __name__ == "__main__":
    init_db() 